# -*- coding: utf-8 -*-
#Database Library 

import sqlite3, os, datetime

class libdatabase:
    def __init__(self):
        self.a = None #SQLite Connector
        self.c = None #cursor
       # self.start()
    def start(self):
        self.connect()
        #Personal DATA
        self.c.execute('''CREATE TABLE personaldata (id INTEGER PRIMARY KEY,name TEXT,
                           gender TEXT, birthdate DATE, age INTEGER, address TEXT, town TEXT,
                           province TEXT, telhome TEXT, telwork TEXT, telwork_internal TEXT, telgsm TEXT,
                           description TEXT)''')
        #Exam DATA
        self.c.execute(''' CREATE TABLE examdata (id INTEGER PRIMARY KEY, personalid INTEGER, examdate DATE,
                           examinfo TEXT, examdescription TEXT, treatmenttype INTEGER)''')
        #Check DATA
        self.c.execute('''CREATE TABLE checkdata (id INTEGER PRIMARY KEY, personalid INTEGER,  examid INTEGER, checkdate DATE,
                          symptoms TEXT, nextcheck DATE)''')
        #Operation DATA
        self.c.execute('''CREATE TABLE operationdata (id INTEGER PRIMARY KEY, personalid INTEGER,  examid INTEGER, operationdate DATE,
                          oprerationnote TEXT, materialid INTEGER)''')
        #Pictures DATA
        self.c.execute('''CREATE TABLE pictures (id INTEGER PRIMARY KEY, personalid INTEGER, examid INTEGER, picturedate DATE, picturetype TEXT, 
                          picturepath TEXT)''')
        #Materials DATA
        self.c.execute('''CREATE TABLE materials (id INTEGER PRIMARY KEY,  operationid INTEGER, materials TEXT, number INTEGER,
                          company TEXT, brand TEXT)''')
        self.commitclose()
    
    def commitclose(self):    
        self.a.commit()
        self.c.close()
              
    def connect(self):
        self.a = sqlite3.connect("patient.db")
        self.c = self.a.cursor()
    def showNames(self):
        self.connect()
        self.c.execute("SELECT name FROM personaldata")
        row = [] 
        for i in self.c:
            row.append(i[0])
        self.c.close()
        return row
    def getCheckData(self,examid, personalid):
        self.connect()
        self.c.execute("""SELECT checkdata.id, checkdata.personalid, checkdata.examid, checkdata.checkdate, checkdata.symptoms, checkdata.nextcheck
                       FROM examdata JOIN checkdata ON (examdata.id =  checkdata.examid) WHERE examdata.id  = %s""" % str(examid))
        row = []
        for i in self.c:
            if i[1] == personalid:
	        row.append(i)
	self.c.close()

	return row
    def getIDs(self):
        self.connect()
        self.c.execute("SELECT id, name FROM personaldata")
        row = []
        for i in self.c:
            row.append(i)
        self.c.close()
        return row
    def getExamData(self, id):
        self.connect()
        self.c.execute("SELECT examdata.id, examdata.examdate, examdata.examinfo,  examdata.examdescription, examdata.treatmenttype FROM personaldata JOIN examdata ON (personaldata.id = examdata.personalid) WHERE personaldata.id = %s" % str(id))
        row = []
        for i in self.c:
            row.append(i)
        self.c.close()
        return row
    def getByID(self, id):
        self.connect()
        self.c.execute("SELECT * FROM personaldata WHERE id = %s" % str(id))
        row = [] 
        for i in self.c:
            row.append(i)
        return row
    def getNameData(self, name):
        self.connect()
        self.c.execute("SELECT * FROM personaldata WHERE name = '%s'" % name)
        row = []
        for i in self.c:
	    row.append(i)
	self.c.close()
	return row
    def ageCalculator(self, birthdate):
        a = datetime.datetime.now()
        todays = a.year*365 + a.month*30 + a.day
        birth = birthdate.split('.')
        birth = int(birth[0]) + int(birth[1])*30 + int(birth[2])*365
        last = (todays-birth)/365
        return last
    
    def addPersonaldata(self, name="", gender="", birthdate="",address="",
                               town="", province="", telhome="", telwork="", 
                              telwork_internal="", telgsm="", description=""):
        #a.addPersonaldata("Taha Dogan Gunes","1","05.02.1994","Toki","Tokat","Merkez","02123123123","12312312","1231231","123123","iyi hasta")
        
        self.connect()
        command = '''INSERT INTO personaldata (name,gender,birthdate,age,address,
                     town,province,telhome,telwork,telwork_internal,telgsm, description) VALUES
                     ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')''' % ( name,
                                                                                               gender,
                                                                                               birthdate,
                                                                                               self.ageCalculator(birthdate),
                                                                                               address,
                                                                                               town,
                                                                                               province,
                                                                                               telhome,
                                                                                               telwork,
                                                                                               telwork_internal,
                                                                                               telgsm,
                                                                                               description)
        self.c.execute(command)
        self.commitclose() 
    def addExamdata(self, personalid=0, examdate="", examinfo="", examdescription="", treatmenttype=0):
        #a.addExamdata(1,"01.02.03","very nice","nice","good","bad")

        self.connect()
        command = ''' INSERT INTO examdata (personalid, examdate, examinfo, examdescription, treatmenttype) VALUES
                      (%s,'%s','%s','%s','%s')''' % (personalid, examdate, examinfo, examdescription, treatmenttype)
        #print command
        self.c.execute(command)
        self.commitclose()
    
    def addCheckdata(self, personalid=0,  examid=0, checkdate="",symptoms="", nextcheck=0):
        #a.addCheckdata(1,1,"01.02.02","something",3)
        
        self.connect()
        command = ''' INSERT INTO checkdata (personalid,examid,checkdate,symptoms,nextcheck) VALUES
                      (%s,%s,'%s','%s',%s)''' % (personalid, examid, checkdate, symptoms, nextcheck)
        #print command
        self.c.execute(command)
        self.commitclose()
    
    def addOperationdata(self,personalid=0,  examid=0, operationdate="", prerationnote="", materialid=0):
        #a.addOperation(1,1,"01.02.03","nice",1)
        
        self.connect()
        command = ''' INSERT INTO operationdata (personalid, examid, operationdate, prerationnote, materialid) VALUES
                      (%s,%s,'%s','%s',%s)''' % (personalid, examid, operationdate, prerationnote, materialid)
        self.c.execute(command)
        self.commitclose()
    
    def addPicture(self, personalid=0, examid=0, picturedate="", picturetype="", picturepath=""):
        #a.addPicture(1,1,'01.02.03","ankle","/home/tdgunes/pics/a.jpg")
       
        self.connect()
        command = ''' INSERT INTO pictures (personalid, examid, picturedate, picturetype, picturepath) VALUES
                      (%s,%s,'%s','%s','%s')''' % (personalid, examid, picturedate, picturetype, picturepath)
        self.c.execute(command)
        self.commitclose()
    
    def addMaterial(self, operationid=0, materials="", number=0,company="", brand=""):
        self.connect()
        command = ''' INSERT INTO materials (operationid, materials, number, company, brand) VALUES
                      (%s,'%s',%s,'%s','%s')''' % (operationid, materials, number, company, brand)
        self.c.execute(command)
        self.commitclose()



